using System;
using System.Text;
using System.Reflection;
using System.Collections.Generic;
using Microsoft.VisualStudio.TestTools.UnitTesting;

using Open.Data.Persistence;


namespace Open.Data.Persistence.Testing
{
    [TestClass]
    public class QueryTests
    {
        //Initialise test class
        [ClassInitialize()]
        public static void ClassInitialize(TestContext testContext)
        {
            Command command = Singleton.Instance.GetContext().CreateCommand();

            Customer customer = new Customer();
            customer.Id = 30;
            customer.FirstNames = "Frank";
            customer.CreditLimit = 90;
            customer.CustomerType = CustomerType.Standard;

            customer.Insert(command);

            customer = new Customer();
            customer.Id = 31;
            customer.FirstNames = "Bob";
            customer.CreditLimit = 12;
            customer.CustomerType = CustomerType.Reseller;

            customer.Phone = new Phone();
            customer.Phone.Id = 999;
            customer.Phone.Insert(command);

            customer.Insert(command);

            Order order = new Order();
            order.Id = 9;
            order.Insert(command);

            CustomerOrder co = new CustomerOrder();
            co.CustomerId = 30;
            co.OrderId = 9;
            co.Insert(command);

            Product product = new Product();
            product.Id = 901;
            product.Description = "Cucumbers";
            product.Insert(command);

            OrderItem item = new OrderItem();
            item.OrderId = order.Id;
            item.ProductId = product.Id;
            item.Insert(command);

            product = new Product();
            product.Id = 902;
            product.Description = "Grapes";
            product.Insert(command);

            item = new OrderItem();
            item.OrderId = order.Id;
            item.ProductId = product.Id;
            item.Insert(command);

            product = new Product();
            product.Id = 903;
            product.Description = "Pears";
            product.Insert(command);

            item = new OrderItem();
            item.OrderId = order.Id;
            item.ProductId = product.Id;
            item.Insert(command);

            product = new Product();
            product.Id = 904;
            product.Description = "Oranges";
            product.Insert(command);

            item = new OrderItem();
            item.OrderId = order.Id;
            item.ProductId = product.Id;
            item.Insert(command);

            product = new Product();
            product.Id = 905;
            product.Description = "Bananas";
            product.Insert(command);

            item = new OrderItem();
            item.OrderId = order.Id;
            item.ProductId = product.Id;
            item.Insert(command);

            //Only tomatos have a weight of 100
            product = new Product();
            product.Id = 906;
            product.Weight = 100;
            product.Description = "Tomatos";
            product.Insert(command);

            item = new OrderItem();
            item.OrderId = order.Id;
            item.ProductId = product.Id;
            item.Insert(command);

            //Add customer address records
            Address address = new Address();
            address.Id = 2;
            address.Line1 = "1 Tree Way";
            address.Insert(command);

            CustomerAddress ca = new CustomerAddress();
            ca.CustomerId = customer.Id;
            ca.AddressId = address.Id;
            ca.Insert(command);

            //Add another order
            order = new Order();
            order.Id = 19;

            order.ShippingAddress = new ShippingAddress();
            order.ShippingAddress.Id = 999;
            order.ShippingAddress.Line1 = "Line1";
            order.ShippingAddress.Insert(command);

            order.Insert(command);
            
            co = new CustomerOrder();
            co.CustomerId = 31;
            co.OrderId = 19;
            co.Insert(command);

            item = new OrderItem();
            item.OrderId = 19;
            item.ProductId = 905;
            item.Insert(command);

            item = new OrderItem();
            item.OrderId = 19;
            item.ProductId = 906;
            item.Insert(command);

            //Add another 2 orders with the same shipping address
            ShippingAddress shipping = new ShippingAddress();
            shipping.Id = 998;
            shipping.Line1 = "Line1";
            shipping.Insert(command);

            order = new Order();
            order.Id = 501;
            order.ShippingAddress = shipping;
            order.Insert(command);

            order = new Order();
            order.Id = 502;
            order.ShippingAddress = shipping;
            order.Insert(command);
        }

        //Query with object properties
        [TestMethod]
        public void QuerySimpleQueryBinder()
        {
            //Simple query
            Query<Customer> query = new Query<Customer>();
            QueryDataAdapter adapter = Singleton.Instance.GetContext().CreateQueryDataAdapter();
            DataBinder<Customer> reader = query.GetBinder(adapter);

            while (reader.Read())
            {
                Assert.IsFalse(reader.Item == null, "Could not iterate query results.");
                reader.Item.Update(adapter.Command);
            }

            reader.Dispose();
        }

        //Query with object properties
        [TestMethod]
        public void QuerySimpleQuery()
        {
            //Simple query
            Query<Customer> query = new Query<Customer>();
            QueryDataAdapter adapter = Singleton.Instance.GetContext().CreateQueryDataAdapter();
            DataBinder<Customer> reader = query.GetBinder(adapter);

            foreach (Customer customer in reader.ToList())
            {
                Assert.IsFalse(customer == null, "Could not iterate query results.");
                customer.Update(adapter.Command);
            }

            //Test top query
            Assert.IsTrue(Common.GetRootQuery(query) == query, "Root query not returned correctly.");
            reader.Dispose();
        }

        //Query with where
        [TestMethod]
        public void QuerySimpleQueryWhere()
        {
            //Simple query
            Query<Customer> query = new Query<Customer>();
            Column column = query.GetColumn("FirstNames");
            query.Where.Add(new Comparison(column, new StringParameter("James")));
            query.Where.Add(new NotNullComparison(query.GetColumn("Id")));

            Command command = Singleton.Instance.GetContext().CreateCommand();
            QueryDataAdapter adapter = query.Execute(command);
            DataBinder<Customer> binder = query.GetBinder(adapter);

            foreach (Customer customer in binder.ToList())
            {
                Assert.IsFalse(customer == null, "Could not iterate query results.");
                customer.Update(adapter.Command);
            }

            binder.Dispose();
        }

        //Query with where anded
        [TestMethod]
        public void QuerySimpleQueryWhereAnd()
        {
            //Simple query
            Query<Customer> query = new Query<Customer>();
            Column column = query.GetColumn("FirstNames");
            query.Where.Add(new Comparison(column, new StringParameter("James")));
            query.Where.Add(new NotNullComparison(query.GetColumn("Id")));

            Command command = Singleton.Instance.GetContext().CreateCommand();
            QueryDataAdapter adapter = query.Execute(command);
            DataBinder<Customer> reader = query.GetBinder(adapter);

            foreach (Customer customer in reader.ToList())
            {
                Assert.IsFalse(customer == null, "Could not iterate query results.");
                customer.Update(adapter.Command);
            }

            reader.Dispose();
        }

        //Query with where
        [TestMethod]
        public void QuerySimpleQueryWhereLike()
        {
            //Simple query
            Query<Product> query = new Query<Product>();
            Column column = query.GetColumn("Description");
            query.Where.AddComparison(column ,new StringParameter("g%"),ComparisonOperator.Like);

            QueryDataAdapter adapter = Singleton.Instance.GetContext().CreateQueryDataAdapter();
            DataBinder<Product> reader = query.GetBinder(adapter);
            List<Product> results = reader.ToList();

            Assert.IsTrue(results.Count == 1, "Starts with condition failed for simple query.");

            //Simple query
            query = new Query<Product>();
            query.Where.Add(new Comparison(column, new StringParameter("%range%"), ComparisonOperator.Like));

            adapter.Reset();
            reader = query.GetBinder(adapter);
            results = reader.ToList();

            Assert.IsTrue(results.Count == 1, "Contains condition failed for simple query.");

            reader.Dispose();
        }

        //Query with order by
        [TestMethod]
        public void QuerySimpleOrderBy()
        {
            //Simple query
            Query<Customer> query = new Query<Customer>();
            query.OrderBy.Add(query.GetColumn("CreditLimit"));

            QueryDataAdapter adapter = Singleton.Instance.GetContext().CreateQueryDataAdapter();
            DataBinder<Customer> reader = query.GetBinder(adapter);

            foreach (Customer customer in reader.ToList())
            {
                Assert.IsFalse(customer == null, "Could not iterate query results.");
                customer.Update(adapter.Command);
            }

            reader.Dispose();
        }

        //Query with object properties
        [TestMethod]
        public void QueryQueryWithObjectProperties()
        {
            //Simple query
            Query<Order> query = new Query<Order>();
            QueryDataAdapter adapter = Singleton.Instance.GetContext().CreateQueryDataAdapter();
            DataBinder<Order> reader = query.GetBinder(adapter);

            int addresses = 0;

            foreach (Order order in reader.ToList())
            {
                Assert.IsTrue(order != null);
                if (order.ShippingAddress != null && order.ShippingAddress.Line1 != null && order.ShippingAddress.Line1 != string.Empty) addresses++;
            }

            Assert.IsTrue(addresses > 0, "No Object property records (Shipping Address) retrieved.");

            reader.Dispose();
        }

        //Simple query
        [TestMethod]
        public void QueryCustomerOrderProducts()
        {
            Query<Customer> customers = new Query<Customer>();
            Query<CustomerOrder> cos = new Query<CustomerOrder>();
            Query<Order> orders = new Query<Order>();
            Query<OrderItem> ois = new Query<OrderItem>();
            Query<Product> products = new Query<Product>();

            customers.Join(cos,"Id","CustomerId");
            cos.Join(orders,"OrderId","Id");
            orders.Join(ois,"Id","OrderId");
            ois.Join(products,"ProductId","Id");

            QueryDataAdapter adapter = Singleton.Instance.GetContext().CreateQueryDataAdapter();

            DataBinder<Customer> customerResults = customers.GetBinder(adapter);
            DataBinder<CustomerOrder> cosResults = cos.GetBinder(adapter);
            DataBinder<Order> ordersResults = orders.GetBinder(adapter);
            DataBinder<OrderItem> oisResults = ois.GetBinder(adapter);
            DataBinder<Product> productsResults = products.GetBinder(adapter);

            Assert.IsTrue(customerResults.ToList().Count == 2, "Customers do not total 2");
            Assert.IsTrue(cosResults.ToList().Count == 2, "Customer Orders do not total 2");
            Assert.IsTrue(ordersResults.ToList().Count == 2, "Orders do not total 2");
            Assert.IsTrue(oisResults.ToList().Count == 8, "Total Order Items do not total 8");
            Assert.IsTrue(productsResults.ToList().Count == 6,"Total products do not total 6");

            Assert.IsTrue(customerResults.ToList()[0] is Customer, "Customer result is not a customer object");
            Assert.IsTrue(cosResults.ToList()[0] is CustomerOrder, "Customer order result is not a customer order object");
            Assert.IsTrue(ordersResults.ToList()[0] is Order, "Order result is not an order object");
            Assert.IsTrue(oisResults.ToList()[0] is OrderItem, "Order item result is not an order item object");
            Assert.IsTrue(productsResults.ToList()[0] is Product, "Product result is not a product object");

            //Test top query
            Assert.IsTrue(Common.GetRootQuery(products) == customers, "Root query not returned correctly.");
            Assert.IsTrue(Common.GetRootQuery(orders) == customers, "Root query not returned correctly.");
        }

        //Simple query
        [TestMethod]
        public void QueryCustomerOrderProductsAddress()
        {
            Query<Customer> customerQuery = new Query<Customer>();
            Query<CustomerOrder> customerOrderQuery = new Query<CustomerOrder>();
            Query<Order> orderQuery = new Query<Order>();
            Query<OrderItem> orderItemQuery = new Query<OrderItem>();
            Query<Product> productQuery = new Query<Product>();

            Query<CustomerAddress> customerAddressQuery = new Query<CustomerAddress>();
            Query<Address> addressQuery = new Query<Address>();

            customerQuery.Join(customerOrderQuery,"Id", "CustomerId");
            customerOrderQuery.Join(orderQuery,"OrderId", "Id");
            orderQuery.Join(orderItemQuery,"Id", "OrderId");
            orderItemQuery.Join(productQuery,"ProductId", "Id");

            customerQuery.Join(customerAddressQuery,"Id","CustomerId");
            customerAddressQuery.Join(addressQuery,"AddressId","Id");

            QueryDataAdapter adapter = Singleton.Instance.GetContext().CreateQueryDataAdapter();
            DataBinder<CustomerAddress> customerAddressBinder = customerAddressQuery.GetBinder(adapter);
            DataBinder<Address> addressBinder = addressQuery.GetBinder(adapter);

            Assert.IsTrue(customerAddressBinder.ToList().Count == 1, "Customer Address objects count not = 1");
            Assert.IsTrue(addressBinder.ToList().Count == 1, "Address objects count not = 1");

            Assert.IsTrue(customerAddressBinder.ToList()[0] is CustomerAddress, "Customer Address result is not a CustomerAddress object.");
            Assert.IsTrue(addressBinder.ToList()[0] is Address, "Address result is not an address.");
        }

        //Query with view tests
        [TestMethod]
        public void QueryQueryView()
        {
            //Simple query
            Query<CustomerView> query = new Query<CustomerView>();
            QueryDataAdapter adapter = Singleton.Instance.GetContext().CreateQueryDataAdapter();
            DataBinder<CustomerView> customerBinder = query.GetBinder(adapter);

            foreach (CustomerView view in customerBinder.ToList())
            {
                Assert.IsTrue(view != null);
            }

            //Simple query with condition
            query = new Query<CustomerView>();
            Column column = query.GetColumn("FirstNames");
            
            query.Where.Add(new Comparison(column, new StringParameter("Frank")));
            adapter.Reset();
            customerBinder = query.GetBinder(adapter);

            CustomerView customer = (CustomerView) customerBinder.ToList()[0];
            Assert.IsTrue(customer.FirstNames == "Frank", "Could not validate conditional view collection");

            //Join a customer view to address
            query = new Query<CustomerView>();
            query.Where.Add(new Comparison(column,new StringParameter("Bob")));

            Query<CustomerAddress> ca = new Query<CustomerAddress>();
            Query<Address> address = new Query<Address>();

            query.Join(ca, "Id", "CustomerId");
            ca.Join(address, "AddressId", "Id");

            adapter.Reset();
            customerBinder = query.GetBinder(adapter);

            customer = (CustomerView) customerBinder.ToList()[0];
            Assert.IsTrue(customer.FirstNames == "Bob", "Could not validate conditional joined view collection");
        }

        [TestMethod]
        public void QuerySimpleExecute()
        {
            Command command = Singleton.Instance.GetContext().CreateCommand();
            Query<Person> personQuery = new Query<Person>();

            //Include this condition to exclude reference properties
            //personQuery.Where.Add(new Condition(typeof(Person), "Phone", null, Comparison.IsNull));
            
            using (QueryDataAdapter adapter = personQuery.Execute(command))
            { 
                while (adapter.Read())
                {
                    Assert.IsTrue(personQuery.Item != null, "Simple query execute returned null reference.");
                }
            }
        }

        //Test that a reference object is created less times than a referencing object
        [TestMethod]
        public void QueryReferenceExecute()
        {
            Command command = Singleton.Instance.GetContext().CreateCommand();
            Query<Order> orderQuery = new Query<Order>();
            List<ShippingAddress> addresses = new List<ShippingAddress>();
            int records = 0;

            using (QueryDataAdapter adapter = orderQuery.Execute(command))
            {
                while (adapter.Read())
                {
                    if (orderQuery.Item.ShippingAddress != null)
                    {   
                        records++;
                        if (!addresses.Contains(orderQuery.Item.ShippingAddress)) addresses.Add(orderQuery.Item.ShippingAddress);
                    }
                }
            }

            //This tests that during a read, the address object is shared when it is not null
            Assert.IsTrue(addresses.Count < records, "Shipping address count does not match order count.");

            records = 0;
            addresses.Clear();

            orderQuery = new Query<Order>();

            using (QueryDataAdapter adapter = orderQuery.Execute(command))
            {
                foreach (Order order in orderQuery.Binder.ToList())
                {
                    if (order.ShippingAddress != null)
                    {
                        records++;
                        if (!addresses.Contains(order.ShippingAddress)) addresses.Add(order.ShippingAddress);
                    }
                }
            }

            //This tests that once a list is created, all object references are shared
            Assert.IsTrue(addresses.Count < records, "Shipping address count is not less than order count.");
        }

        [TestMethod]
        public void QueryInheritedExecute()
        {
            Command command = Singleton.Instance.GetContext().CreateCommand();
            Query<Customer> customerQuery = new Query<Customer>();

            using (QueryDataAdapter adapter = customerQuery.Execute(command))
            {
                while (adapter.Read())
                {
                    Assert.IsTrue(customerQuery.Item != null, "Simple inherited query execute returned null reference.");
                }
            }
        }

        [TestMethod]
        public void QueryNullConditionTests()
        {
            Command command = Singleton.Instance.GetContext().CreateCommand();
            Query<Order> orderQuery = new Query<Order>();

            //Count all orders
            orderQuery.Execute(command);
            int allOrders = orderQuery.Binder.ToList().Count;

            Query<Order> ordersWithShipping = new Query<Order>();
            Column column = orderQuery.GetColumn("ShippingAddress");
            ordersWithShipping.Where.Add(new NotNullComparison(column));

            //Count orders with shipping
            ordersWithShipping.Execute(command);
            int shippingCount = ordersWithShipping.Binder.ToList().Count;

            Query<Order> ordersNoShipping = new Query<Order>();
            ordersNoShipping.Where.Add(new NullComparison(column));

            //Count orders with shipping
            ordersNoShipping.Execute(command);
            int noShippingCount = ordersNoShipping.Binder.ToList().Count;

            Assert.IsTrue(allOrders > 0, "No orders were found.");
            Assert.IsTrue(shippingCount > 0, "No orders with shipping found.");
            Assert.IsTrue(noShippingCount > 0, "No orders without shipping were found.");
            Assert.IsTrue(allOrders == shippingCount + noShippingCount, "Orders count does not match shipping + no shipping count.");
        }

        [TestMethod]
        public void QuerySimpleAggregation()
        {
            //Simple query
            Command command = Singleton.Instance.GetContext().CreateCommand();
            Query<Product> query = new Query<Product>();

            query.GroupBy.Add(new Aggregate(typeof(Product), "Weight", AggregateType.Max));

            QueryDataAdapter adapter = query.Execute(command);
            var list = query.Binder.ToList();

            Assert.IsTrue(list.Count > 0, "Group by query returned no products");
        }

        [TestMethod]
        public void QueryInheritedAggregation()
        {
            //Simple query
            Command command = Singleton.Instance.GetContext().CreateCommand();
            Query<Customer> query = new Query<Customer>();

            query.GroupBy.Add(new Aggregate(typeof(Customer), "CreditLimit", AggregateType.Max));

            QueryDataAdapter adapter = query.Execute(command);
            var list = query.Binder.ToList();

            Assert.IsTrue(list.Count > 0, "Group by query returned no customers");
        }

        [TestMethod]
        public void QueryViewAggregation()
        {
            //Simple query
            Command command = Singleton.Instance.GetContext().CreateCommand();
            Query<PersonView> query = new Query<PersonView>();

            query.GroupBy.Add(new Aggregate(typeof(Person), "Id", AggregateType.Min));

            QueryDataAdapter adapter = query.Execute(command);
            var list = query.Binder.ToList();

            Assert.IsTrue(list.Count > 0, "Group by query returned no person views");
        }

        [TestMethod]
        public void QueryViewAggregationSum()
        {
            //Simple query
            Command command = Singleton.Instance.GetContext().CreateCommand();
            Query<ProductView> query = new Query<ProductView>();

            query.GroupBy.Add(new Aggregate(typeof(Product), "Id", AggregateType.Min));
            query.GroupBy.Add(new Aggregate(typeof(Product), "Weight", AggregateType.Sum));

            QueryDataAdapter adapter = query.Execute(command);
            var list = query.Binder.ToList();

            Assert.IsTrue(list.Count == 1, "Product view was not aggregated correctly.");
            Assert.IsTrue(list[0].Weight == 100, "Product sum not calculated correctly");
        }

        [TestMethod]
        public void QueryJoinAggregation()
        {
            //Simple query
            Command command = Singleton.Instance.GetContext().CreateCommand();
            var orderQuery = new Query<Order>();
            var orderItemQuery = new Query<OrderItem>();

            orderQuery.Join(orderItemQuery, "Id", "OrderId");
            orderQuery.GroupBy.Add(new Aggregate(typeof(OrderItem), "Quantity", AggregateType.Sum));
            orderQuery.GroupBy.Add(new Aggregate(typeof(OrderItem), "ProductId", AggregateType.Min));

            QueryDataAdapter adapter = orderQuery.Execute(command);
            var orderList = orderQuery.GetBinder(adapter).ToList();
            var orderItemList = orderItemQuery.GetBinder(adapter).ToList();

            Assert.IsTrue(orderList.Count == 2, "Orders not aggregated correctly.");
            Assert.IsTrue(orderItemList.Count == 2, "Order items not aggregated correctly.");
        }

        [TestMethod]
        public void QuerySimpleCount()
        {
            //Simple query
            Command command = Singleton.Instance.GetContext().CreateCommand();
            Query<Product> query = new Query<Product>();
            int count = query.Count(command);

            Assert.IsTrue(count > 0, "Simple query count returned zero rows.");
        }

        [TestMethod]
        public void QuerySimpleAggregateCount()
        {
            //Simple query
            Command command = Singleton.Instance.GetContext().CreateCommand();
            Query<Product> query = new Query<Product>();
            
            query.GroupBy.Add(new Aggregate(typeof(Product), "Weight", AggregateType.Max));
            int count = query.Count(command);

            Assert.IsTrue(count > 0, "Simple query aggregrate count returned zero rows.");
        }
    }
}
